Skip Headers

Oracle® Database Advanced Replication Management API Reference 10g Release 2 (10.2) Part Number B14227-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 16 DBMS_RECTIFIER_DIFF

The `DBMS_RECTIFIER_DIFF` package contains APIs used to detect and resolve data inconsistencies between two replicated sites.

This chapter contains this topic:

- Summary of DBMS_RECTIFIER_DIFF Subprograms

## Summary of DBMS_RECTIFIER_DIFF Subprograms

Table 16-1 DBMS_RECTIFIER_DIFF Package Subprograms

Subprogram | Description
--- | ---
"DIFFERENCES Procedure" | Determines the differences between two tables.
"RECTIFY Procedure" | Resolves the differences between two tables.

### DIFFERENCES Procedure

This procedure determines the differences between two tables. It accepts the storage table of a nested table.

Note: This procedure cannot be used on LOB columns, nor on columns based on user-defined types.

Syntax

    DBMS_RECTIFIER_DIFF.DIFFERENCES (
       sname1               IN  VARCHAR2,
       oname1               IN  VARCHAR2,
       reference_site       IN  VARCHAR2 := '',
       sname2               IN  VARCHAR2,
       oname2               IN  VARCHAR2,
       comparison_site      IN  VARCHAR2 := '',
       where_clause         IN  VARCHAR2 := '',
       { column_list        IN  VARCHAR2 := '',
       | array_columns      IN  DBMS_UTILITY.NAME_ARRAY, }
       missing_rows_sname   IN  VARCHAR2,
       missing_rows_oname1  IN  VARCHAR2,
       missing_rows_oname2  IN  VARCHAR2,
       missing_rows_site    IN  VARCHAR2 := '',
       max_missing          IN  INTEGER,
       commit_rows          IN  INTEGER := 500);

Note: This procedure is overloaded. The `column_list` and `array_columns` parameters are mutually exclusive.

Parameters

Table 16-2 DIFFERENCES Procedure Parameters

Parameter | Description
--- | ---
    sname1
| Name of the schema at `reference_site`.
    oname1
| Name of the table at `reference_site`.
    reference_site
| Name of the reference database site. The default, `NULL`, indicates the current site.
    sname2
| Name of the schema at `comparison_site`.
    oname2
| Name of the table at `comparison_site`.
    comparison_site
| Name of the comparison database site. The default, `NULL`, indicates the current site.
    where_clause
| Only rows satisfying this clause are selected for comparison. The default, `NULL`, indicates all rows are compared.
    column_list
| A comma-delimited list of one or more column names being compared for the two tables. You must not have any spaces before or after a comma. The default, `NULL`, indicates that all columns will be compared.
    array_columns
| A PL/SQL index-by table of column names being compared for the two tables. Indexing begins at 1, and the final element of the array must be `NULL`. If position 1 is `NULL`, then all columns are used.
    missing_rows_sname
| Name of the schema containing the tables with the missing rows.
    missing_rows_oname1
| Name of an existing table at `missing_rows_site` that stores information about the rows in the table at `reference_site` that are missing from the table at `comparison_site`, and information about the rows at `comparison_site` site that are missing from the table at `reference_site`.
    missing_rows_oname2
| Name of an existing table at `missing_rows_site` that stores information about the missing rows. This table has three columns: the `R_ID` column shows the rowid of the row in the `missing_rows_oname1` table, the `PRESENT` column shows the name of the site where the row is present, and the `ABSENT` column shows name of the site from which the row is absent.
    missing_rows_site
| Name of the site where the `missing_rows_oname1` and `missing_rows_oname2` tables are located. The default, `NULL`, indicates that the tables are located at the current site.
    max_missing
| Integer that specifies the maximum number of rows that should be inserted into the `missing_rows_oname` table. If more than `max_missing` rows are missing, then that many rows are inserted into `missing_rows_oname`, and the routine then returns normally without determining whether more rows are missing. This parameter is useful if the fragments are so different that the missing rows table has too many entries and there is no point in continuing. Raises exception `badnumber` if `max_missing` is less than 1 or `NULL`.
    commit_rows
| Maximum number of rows to insert to or delete from the reference or comparison table before a `COMMIT` occurs. By default, a `COMMIT` occurs after 500 inserts or 500 deletes. An empty string (`'` `'`) or `NULL` indicates that a `COMMIT` should be issued only after all rows for a single table have been inserted or deleted.

Exceptions

Table 16-3 DIFFERENCES Procedure Exceptions

Exception | Description
--- | ---
    nosuchsite
| Database site could not be found.
    badnumber
| The `commit_rows` parameter is less than 1.
    missingprimarykey
| Column list must include primary key (or `SET_COLUMNS` equivalent).
    badname
| `NULL` or empty string for table or schema name.
    cannotbenull
| Parameter cannot be `NULL`.
    notshapeequivalent
| Tables being compared are not shape equivalent. Shape refers to the number of columns, their column names, and the column datatypes.
    unknowncolumn
| Column does not exist.
    unsupportedtype
| Type not supported.
    dbms_repcat.commfailure
| Remote site is inaccessible.
    dbms_repcat.missingobject
| Table does not exist.

Restrictions

The error `ORA-00001` (unique constraint violated) is issued when there are any unique or primary key constraints on the missing rows table.

### RECTIFY Procedure

This procedure resolves the differences between two tables. It accepts the storage table of a nested table.

Note: This procedure cannot be used on LOB columns, nor on columns based on user-defined types.

Syntax

    DBMS_RECTIFIER_DIFF.RECTIFY (
       sname1               IN  VARCHAR2,
       oname1               IN  VARCHAR2,
       reference_site       IN  VARCHAR2 := '',
       sname2               IN  VARCHAR2,
       oname2               IN  VARCHAR2,
       comparison_site      IN  VARCHAR2 := '',
       { column_list        IN  VARCHAR2 := '',
       | array_columns      IN  dbms_utility.name_array, }
       missing_rows_sname   IN  VARCHAR2,
       missing_rows_oname1  IN  VARCHAR2,
       missing_rows_oname2  IN  VARCHAR2,
       missing_rows_site    IN  VARCHAR2 := '',
       commit_rows          IN  INTEGER := 500);

Note: This procedure is overloaded. The `column_list` and `array_columns` parameters are mutually exclusive.

Parameters

Table 16-4 RECTIFY Procedure Parameters

Parameter | Description
--- | ---
    sname1
| Name of the schema at `reference_site`.
    oname1
| Name of the table at `reference_site`.
    reference_site
| Name of the reference database site. The default, `NULL`, indicates the current site.
    sname2
| Name of the schema at `comparison_site`.
    oname2
| Name of the table at `comparison_site`.
    comparison_site
| Name of the comparison database site. The default, `NULL`, indicates the current site.
    column_list
| A comma-delimited list of one or more column names being compared for the two tables. You must not have any spaces before or after a comma. The default, `NULL`, indicates that all columns will be compared.
    array_columns
| A PL/SQL index-by table of column names being compared for the two tables. Indexing begins at 1, and the final element of the array must be `NULL`. If position 1 is `NULL`, then all columns are used.
    missing_rows_sname
| Name of the schema containing the tables with the missing rows.
    missing_rows_oname1
| Name of the table at `missing_rows_site` that stores information about the rows in the table at `reference_site` that are missing from the table at `comparison_site`, and information about the rows at `comparison_site` that are missing from the table at `reference_site`.
    missing_rows_oname2
| Name of the table at `missing_rows_site` that stores information about the missing rows. This table has three columns: the rowid of the row in the `missing_rows_oname1` table, the name of the site at which the row is present, and the name of the site from which the row is absent.
    missing_rows_site
| Name of the site where the `missing_rows_oname1` and `missing_rows_oname2` tables are located. The default, `NULL`, indicates that the tables are located at the current site.
    commit_rows
| Maximum number of rows to insert to or delete from the reference or comparison table before a `COMMIT` occurs. By default, a `COMMIT` occurs after 500 inserts or 500 deletes. An empty string (`'` `'`) or `NULL` indicates that a `COMMIT` should be issued only after all rows for a single table have been inserted or deleted.

Exceptions

Table 16-5 RECTIFY Procedure Exceptions

Exception | Description
--- | ---
    nosuchsite
| Database site could not be found.
    badnumber
| The `commit_rows` parameter is less than 1.
    badname
| `NULL` or empty string for table or schema name.
    dbms_repcat.commfailure
| Remote site is inaccessible.
    dbms_repcat.missingobject
| Table does not exist.